from tools.funciton import replace_column_values, read_xlsx_file, write_xlsx, read_xls_file
from config.configs import ALPHA_DB_PORT, ALPHA_DB_HOST, DATA_PATH, DINGSTOCK_HOST, DINGSTOCK_PORT, DESKTOP_PATH
import pymongo
from tools.funciton import read_json_file
import json
import os
import time

ding = pymongo.MongoClient(host=DINGSTOCK_HOST, port=DINGSTOCK_PORT, authMechanism='DEFAULT', authSource='admin',
                           directConnection=True)
account = ding.accounting
trade = ding.tradingMarket
custom_file = os.path.join(DATA_PATH, 'ding.xlsx')
bonded_file = os.path.join(DATA_PATH, 'add_spu_datas.json')


def insert_custom_goods(file_path):
    """记账小程序新增自定义商品"""
    title_list = read_xlsx_file(file_path, 0)
    article_number = read_xlsx_file(file_path, 1)
    image = read_xlsx_file(file_path, 2)
    res = []
    pipeline = [
        {
            '$match':
                {
                    'source': "custom"
                }
        },
        {
            '$sort':
                {
                    'skuId': -1
                }
        },
        {
            '$limit': 1
        },
        {
            '$project':
                {
                    'skuId': 1,
                    'spuId': 1
                }
        }
    ]
    [res.append([i['spuId'], i['skuId']]) for i in account.skus.aggregate(pipeline=pipeline)]
    spu = res[0][0] + 1
    sku = res[0][1] + 1
    print(spu, sku)
    spus = []
    length = len(title_list)
    for d in range(length):
        spus.append(spu + d)
    for i in range(length):
        param = {"spuId": spu, "title": title_list[i], "articleNumber": article_number[i], "spuLogo": image[i],
                 "platform": "dc", "type": "digital", "status": 1, "__v": 0, "isBlocked": False,
                 "createdAt": int(round(time.time() * 1000)),
                 "updatedAt": int(round(time.time() * 1000)), "source": "custom"}
        account.spus.insert_one(param)
        spu += 1
    size = read_xlsx_file(file_path, 3)
    sizes = []
    for z in size:
        sizes.append(str(z).split('、'))
    for i in range(len(sizes)):
        for z in range(len(sizes[i])):
            origin_size = "{\"尺码\":\"35.5\"}"
            param = {"spuId": spus[i], "skuId": sku, "properties": origin_size.replace('35.5', sizes[i][z]),
                     "status": 1, "source": "custom",
                     "__v": 0, "isBlocked": False, "createdAt": int(round(time.time() * 1000)),
                     "updatedAt": int(round(time.time() * 1000))}
            account.skus.insert_one(param)
            sku += 1


def dingstock_dev_insert_spu_sku(file_path):
    """新增单个货号尺码-盯潮"""
    spuId = read_json_file(file_path)['data']['contents'][0]['spuId']
    spu_datas = read_json_file(file_path)['data']['contents'][0]['commodityInfo']
    spu_param = {'spuId': spuId, 'title': spu_datas['title'], 'categoryName': spu_datas['categoryName'],
                 'brandName': spu_datas['brandName'], 'brandId': spu_datas['brandId'],
                 'categoryId': spu_datas['categoryId'],
                 'articleNumber': spu_datas['articleNumber'], 'spuLogo': spu_datas['logoUrl'], 'platform': 'dewu',
                 'createdAt': int(round(time.time() * 1000)), 'updatedAt': int(round(time.time() * 1000))}
    account.spus.insert_one(spu_param)
    sku_list = read_json_file(file_path)['data']['contents'][0]['commodityInfo']['skuInfoList']
    for i in range(len(sku_list)):
        items = sku_list[i]['skuPropsList']
        sorted_items = sorted(items, key=lambda x: x['level'])
        new_dict = {item['name']: item['value'] for item in sorted_items}
        json_string = json.dumps(new_dict, ensure_ascii=False)
        sku_param = {"spuId": spuId, "skuId": sku_list[i]['skuId'], "properties": json_string.replace(" ", ""),
                     'barcode': sku_list[i]['barCodeList'][0],
                     'status': 1,
                     "__v": 0, "createdAt": int(round(time.time() * 1000)), "updatedAt": int(round(time.time() * 1000))}
        account.skus.insert_one(sku_param)


# dingstock_dev_insert_spu_sku(file1)


def dingstock_dev_insert_spu_sku_batch(file_path):
    """批量新增货号尺码-盯潮"""
    for i in range(len(read_json_file(file_path)['data']['contents'])):
        spuId = read_json_file(file_path)['data']['contents'][i]['spuId']
        spu_datas = read_json_file(file_path)['data']['contents'][i]['commodityInfo']
        spu_param = {'spuId': spuId, 'title': spu_datas['title'] + ' 草牌', 'categoryName': spu_datas['categoryName'],
                     'brandName': spu_datas['brandName'], 'brandId': spu_datas['brandId'],
                     'categoryId': spu_datas['categoryId'],
                     'articleNumber': spu_datas['articleNumber'], 'spuLogo': spu_datas['logoUrl'], 'platform': 'dewu',
                     'createdAt': int(round(time.time() * 1000)), 'updatedAt': int(round(time.time() * 1000))}
        account.spus.insert_one(spu_param)
        sku_list = read_json_file(file_path)['data']['contents'][i]['commodityInfo']['skuInfoList']
        for z in range(len(sku_list)):
            items = sku_list[z]['skuPropsList']
            sorted_items = sorted(items, key=lambda x: x['level'])
            new_dict = {item['name']: item['value'] for item in sorted_items}
            json_string = json.dumps(new_dict, ensure_ascii=False)
            sku_param = {"spuId": spuId, "skuId": sku_list[z]['skuId'], "properties": json_string.replace(" ", ""),
                         'barcode': sku_list[z]['barCodeList'][0],
                         'status': 1,
                         "__v": 0, "createdAt": int(round(time.time() * 1000)),
                         "updatedAt": int(round(time.time() * 1000))}
            account.skus.insert_one(sku_param)


def trade_insert_custom_goods(file_path):
    """出求小程序新增spu"""
    title_list = read_xlsx_file(file_path, 0)
    article_number = read_xlsx_file(file_path, 0)
    image = read_xlsx_file(file_path, 1)
    brandname = read_xlsx_file(file_path, 2)
    res = []
    pipeline = [
        {
            '$sort':
                {
                    'skuId': -1
                }
        },
        {
            '$limit': 1
        },
        {
            '$project':
                {
                    'skuId': 1,
                    'spuId': 1
                }
        }
    ]
    [res.append([i['spuId'], i['skuId']]) for i in trade.skus.aggregate(pipeline=pipeline)]
    spu = res[0][0] + 1
    sku = res[0][1] + 1
    print(spu, sku)
    spus = []
    length = len(title_list)
    for d in range(length):
        spus.append(spu + d)
    for i in range(length):
        param = {"spuId": spu, "title": title_list[i], "articleNumber": article_number[i], "brandId": 65,
                 "spuLogo": image[i],
                 "platform": "dc", "type": "digital", "status": 1, "__v": 0, "isBlocked": False,
                 "createdAt": int(round(time.time() * 1000)),
                 "updatedAt": int(round(time.time() * 1000))}
        trade.spus.insert_one(param)
        spu += 1
    size = read_xlsx_file(file_path, 4)
    colors = read_xlsx_file(file_path, 5)
    for i in range(len(size)):
        origin_size = "{\"规格\":\"35.5\",\"颜色\":\"36\"}"
        new1 = origin_size.replace('35.5', str(size[i]))
        new2 = new1.replace('36', colors[i])
        param = {"spuId": 72952536, "skuId": sku, "properties": new2,
                 "platform": "dc", "type": "digital", "status": 1, "source": "custom",
                 "__v": 0, "isBlocked": False, "createdAt": int(round(time.time() * 1000)),
                 "updatedAt": int(round(time.time() * 1000))}
        trade.skus.insert_one(param)
        sku += 1


def update_datas():
    f = os.path.join(DESKTOP_PATH, '商品导入模板1.xlsx')
    xilie = read_xlsx_file(f, 3)
    wei = read_xlsx_file(f, 6)
    dic = {}
    for i in range(len(xilie)):
        if xilie[i] not in dic:
            dic[xilie[i]] = wei[i]
        else:
            pass
    li = list(dic.keys())
    va = list(dic.values())
    print(li)
    print(va)
    for z in range(len(li)):
        trade.spus.update_many({"series": str(li[z])}, {"$set": {"weight": va[z]}})


